SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE  VIEW [dbo].[TMCNTA_PAGA] AS   
  
/*************************** SUB SELECT PARA LA TMDOCU_PROV **********************/  
 Select t1.CO_EMPR, t1.CO_PROV, t2.NO_CORT_PROV NO_AUXI, t1.CO_TIPO_DOCU, t1.NU_DOCU_PROV,   
       t1.FE_EMIS, t1.FE_REGI_COMP, t1.FE_VENC, t1.CO_MONE, t1.FA_TIPO_CAMB, t2.NU_RUCS_PROV NU_RUCS,   
      (PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) * t1.IM_TOTA 'IM_TOTA',   
      ((PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) * t1.IM_TOTA ) -  
           (PATINDEX(t8.ST_SIGN, 'S') - PATINDEX(t8.ST_SIGN, 'N')) *  
      ((Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +  
          PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)  
  From TCMOVI_BANC t11, TDMOVI_BANC t12, TMCNTA_BANC t13  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t12.TI_ENTI = 'P'  
  And t1.CO_PROV = t12.CO_ENTI  
  And t1.CO_TIPO_DOCU = t12.TI_DOCU_ENTI  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_ENTI  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_BANC = t12.CO_BANC  
  And t11.NU_CNTA_BANC  = t12.NU_CNTA_BANC  
  And t11.AA_BNCO = t12.AA_BNCO  
  And t11.MM_BNCO = t12.MM_BNCO  
  And t11.TI_MOVI_BANC  = t12.TI_MOVI_BANC  
  And t11.NU_COMP_BANC = t12.NU_COMP_BANC  
  And t11.CO_EMPR = t13.CO_EMPR  
  And t11.CO_BANC = t13.CO_BANC  
  And t11.NU_CNTA_BANC  = t13.NU_CNTA_BANC) +  
  
  (Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +  
      PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)  
  From TCMOVI_CAJA t11, TDMOVI_CAJA t12, TMCAJA t13  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t12.TI_ENTI = 'P'  
  And t1.CO_PROV = t12.CO_ENTI  
  And t1.CO_TIPO_DOCU = t12.TI_DOCU_ENTI  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_ENTI  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_CAJA = t12.CO_CAJA  
  And t11.AA_CAJA = t12.AA_CAJA  
  And t11.MM_CAJA = t12.MM_CAJA  
  And t11.TI_MOVI  = t12.TI_MOVI  
  And t11.NU_COMP_CAJA = t12.NU_COMP_CAJA  
  And t11.CO_EMPR = t13.CO_EMPR  
  And t11.CO_CAJA = t13.CO_CAJA) +  
  
  (Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_PAGO_CDES,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CDES,0)),0)  
  From TCAMAR_NCPR t11, TDAMAR_NCPR t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_TIPO_DOCU = t12.CO_TIDO_DEST  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_DEST  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.NU_AMAR = t12.NU_AMAR  
  And t11.FE_AMAR = t12.FE_AMAR) +  
  
  (Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_PAGO_CORI,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CORI,0)),0)  
  From TCAMAR_NCPR t11, TDAMAR_NCPR t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_TIPO_DOCU = t12.CO_TIDO_ORIG  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_ORIG  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.NU_AMAR = t12.NU_AMAR  
  And t11.FE_AMAR = t12.FE_AMAR) +  
  
  (Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CANJ_MDOC,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)  
  From TCCANJ_PROV t11, TDCANJ_PROV t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_TIPO_DOCU = t12.CO_TIPO_DOCU  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_PROV  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.CO_PROV = t12.CO_PROV  
  And t11.NU_CANJ = t12.NU_CANJ  
  And t11.FE_CANJ = t12.FE_CANJ) +  
        
  (Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_RENO_MDOC,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)  
  From TCRENO_PROV t11, TDRENO_PROV t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_TIPO_DOCU = t12.CO_TIPO_DOCU  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_PROV  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.CO_PROV = t12.CO_PROV  
  And t11.NU_RENO = t12.NU_RENO  
  And t11.FE_RENO = t12.FE_RENO) +  
  
  (Select ISNULL(SUM(ISNULL(t12.IM_PAGO_DEST,0)),0)  
  From TCAMAR_OCPR t11, TDAMAR_OCPR t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_PROV = t12.CO_ENTI  
  And t1.CO_TIPO_DOCU = t12.CO_TIDO_DEST  
  And t1.NU_DOCU_PROV = t12.NU_DOCU_DEST  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.NU_AMAR = t12.NU_AMAR  
  And t11.FE_AMAR = t12.FE_AMAR)) IM_SALD,  
  t1.DE_OBSE, t1.CO_TIPO_OPER  
 From TMDOCU_PROV t1, OFILOGI..TMPROV t2, TTDOCU_CNTB t8  
 Where t1.CO_ESTA_DOCU != 'ANU'  
 And RTRIM(ISNULL(T1.NU_REND_GAST, '')) =  ''  
 And t1.CO_EMPR = t2.CO_EMPR  
 And t1.CO_PROV = t2.CO_PROV  
 And t1.CO_TIPO_DOCU = t8.TI_DOCU  
 And t8.ST_DOCU_0015 = 'N'  
 And t8.ST_DOCU_0017 = 'N'  
  
/**************************** SUB SELECT PARA LETRAS ******************************/  
Union All  
 Select t1.CO_EMPR, t1.CO_PROV, t2.NO_CORT_PROV, 'LET', t1.NU_LETR_PROV,   
  t1.FE_EMIS, t1.FE_EMIS, t1.FE_VENC, t1.CO_MONE, t1.FA_TIPO_CAMB,    
  t2.NU_RUCS_PROV, t1.IM_TOTA, t1.IM_TOTA -  
  ((Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +  
          PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)  
  From TCMOVI_BANC t11, TDMOVI_BANC t12, TMCNTA_BANC t13  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t12.TI_ENTI = 'P'  
  And t12.TI_DOCU_ENTI = 'LET'  
  And t1.CO_PROV = t12.CO_ENTI  
  And t1.NU_LETR_PROV = t12.NU_DOCU_ENTI  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_BANC = t12.CO_BANC  
  And t11.NU_CNTA_BANC  = t12.NU_CNTA_BANC  
  And t11.AA_BNCO = t12.AA_BNCO  
  And t11.MM_BNCO = t12.MM_BNCO  
  And t11.TI_MOVI_BANC  = t12.TI_MOVI_BANC  
  And t11.NU_COMP_BANC = t12.NU_COMP_BANC  
  And t11.CO_EMPR = t13.CO_EMPR  
  And t11.CO_BANC = t13.CO_BANC  
  And t11.NU_CNTA_BANC  = t13.NU_CNTA_BANC) +  
  
  (Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CONV_DOCU,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0)),0)  
  From TCMOVI_CAJA t11, TDMOVI_CAJA t12, TMCAJA t13  
  Where t12.TI_ENTI = 'P'  
  And t12.TI_DOCU_ENTI = 'LET'  
  And t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_PROV = t12.CO_ENTI  
  And t1.NU_LETR_PROV = t12.NU_DOCU_ENTI  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_CAJA = t12.CO_CAJA  
  And t11.AA_CAJA = t12.AA_CAJA  
  And t11.MM_CAJA = t12.MM_CAJA  
  And t11.TI_MOVI  = t12.TI_MOVI  
  And t11.NU_COMP_CAJA = t12.NU_COMP_CAJA  
  And t11.CO_EMPR = t13.CO_EMPR  
  And t11.CO_CAJA = t13.CO_CAJA) +  
  
  (Select ISNULL(SUM(PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_PAGO_CDES,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_CDES,0)),0)  
  From TCAMAR_NCPR t11, TDAMAR_NCPR t12  
  Where t12.CO_TIDO_DEST = 'LET'  
  And t1.CO_EMPR = t12.CO_EMPR  
  And t1.NU_LETR_PROV = t12.NU_DOCU_DEST  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.NU_AMAR = t12.NU_AMAR  
  And t11.FE_AMAR = t12.FE_AMAR) +  
  
  (Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_CANJ_MDOC,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)  
  From TCCANJ_PROV t11, TDCANJ_PROV t12  
  Where t12.CO_TIPO_DOCU ='LET'  
  And t1.CO_EMPR = t12.CO_EMPR  
  And t1.NU_LETR_PROV = t12.NU_DOCU_PROV  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.CO_PROV = t12.CO_PROV  
  And t11.NU_CANJ = t12.NU_CANJ  
  And t11.FE_CANJ = t12.FE_CANJ) +  
        
  (Select ISNULL(SUM( PATINDEX(t12.ST_CIER_DOCU, 'N')* ISNULL(t12.IM_RENO_MDOC,0) +  
         PATINDEX(t12.ST_CIER_DOCU, 'S')* ISNULL(t12.IM_SALD_DOCU,0) ),0)  
  From TCRENO_PROV t11, TDRENO_PROV t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t12.CO_TIPO_DOCU = 'LET'  
  And t1.NU_LETR_PROV = t12.NU_DOCU_PROV  
  And t1.CO_PROV = t11.CO_PROV  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.CO_PROV = t12.CO_PROV  
  And t11.NU_RENO = t12.NU_RENO  
  And t11.FE_RENO = t12.FE_RENO) +  
  
  (Select ISNULL(SUM( ISNULL(t12.IM_PAGO_DEST,0)),0)  
  From TCAMAR_OCPR t11, TDAMAR_OCPR t12  
  Where t12.CO_TIDO_DEST = 'LET'  
  And t1.NU_LETR_PROV = t12.NU_DOCU_DEST  
  And t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_PROV = t12.CO_ENTI  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.NU_AMAR = t12.NU_AMAR  
  And t11.FE_AMAR = t12.FE_AMAR)) IM_SALD,  
  '' DE_OBSE, t1.CO_TIPO_OPER  
 From TMLETR_PROV t1, OFILOGI..TMPROV t2  
 Where t1.CO_ESTA_DOCU != 'ANU'  
 And t1.CO_EMPR = t2.CO_EMPR  
 And t1.CO_PROV = t2.CO_PROV  
  
/************************************** SUB SELECT PARA ANTICIPOS ***************************/  
  
Union All  
 Select t1.CO_EMPR, t1.CO_ENTI, Max(t7.NO_CORT_PROV), t1.TI_DOCU_ENTI, t1.NU_DOCU_ENTI,    
  Max(t2.FE_OPER), Max(t2.FE_OPER), Max(t1.FE_DOCU), Max(t1.CO_MONE_DOCU), Max(t2.FA_TIPO_CAMB), Max(t7.NU_RUCS_PROV),  
  Sum (ISNULL(PATINDEX(t1.ST_CIER_DOCU, 'N')* ISNULL(t1.IM_CONV_DOCU,0) +  
  PATINDEX(t1.ST_CIER_DOCU, 'S')* ISNULL(t1.IM_SALD_DOCU,0),0)),  
  (Sum (ISNULL(PATINDEX(t1.ST_CIER_DOCU, 'N')* ISNULL(t1.IM_CONV_DOCU,0) +  
  PATINDEX(t1.ST_CIER_DOCU, 'S')* ISNULL(t1.IM_SALD_DOCU,0),0)) -  
	 (Select ISNULL(SUM(ISNULL(t12.IM_PAGO_ORIG,0)),0) - ISNULL(SUM(ISNULL(t20.IM_DEVO,0)),0)    
			From TCAMAR_OCPR t11
			inner join  TDAMAR_OCPR t12  on 
			t11.CO_EMPR = t12.CO_EMPR  
			And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
			And t11.NU_AMAR = t12.NU_AMAR  
			And t11.FE_AMAR = t12.FE_AMAR
			And t11.TI_SITU != 'ANU'  
			left outer join TDDEVO_ANTC t20 on 
			t20.co_empr = t1.co_empr 
			and t20.co_banc = t1.co_banc
			and t20.nu_cnta_banc = t1.nu_cnta_banc
			and t20.aa_bnco = t1.aa_bnco
			and t20.mm_bnco = t1.mm_bnco
			and t20.ti_movi_banc = t1.ti_movi_banc
			and t20.nu_comp_banc = t1.nu_comp_banc
			and t20.co_tipo_docu = t1.ti_docu_enti
			and t20.nu_docu_prov = t1.nu_docu_enti
			Where t1.CO_EMPR = t12.CO_EMPR  
			And t1.CO_ENTI = t12.CO_ENTI  
			And t1.TI_DOCU_ENTI = t12.CO_TIDO_ORIG  
			And t1.NU_DOCU_ENTI = t12.NU_DOCU_ORIG )) * -1,  
		
 Max(t2.DE_OBSE), Max(t2.CO_TIPO_OPER)  
 From TDMOVI_BANC t1, TTDOCU_CNTB t4, TCMOVI_BANC t2, TMCNTA_BANC t3, TMPARA_TESO t6, OFILOGI..TMPROV t7  
 Where t1.TI_ENTI = t6.CO_TIAU_PROV  
 And t1.TI_DOCU_ENTI = t4.TI_DOCU  
 And (t4.ST_DOCU_0015 = 'S'  
 Or  t4.ST_DOCU_0017 = 'S')  
 And t2.TI_SITU != 'ANU'  
 And t1.CO_EMPR = t2.CO_EMPR  
 And t1.CO_BANC = t2.CO_BANC  
 And t1.NU_CNTA_BANC  = t2.NU_CNTA_BANC  
 And t1.AA_BNCO = t2.AA_BNCO  
 And t1.MM_BNCO = t2.MM_BNCO  
 And t1.TI_MOVI_BANC  = t2.TI_MOVI_BANC  
 And t1.NU_COMP_BANC = t2.NU_COMP_BANC  
 And t1.CO_EMPR = t3.CO_EMPR  
 And t1.CO_BANC = t3.CO_BANC  
 And t1.NU_CNTA_BANC  = t3.NU_CNTA_BANC  
 And t3.TI_SITU = 'ACT'  
 And t1.CO_EMPR = t6.CO_EMPR  
 And t1.CO_EMPR = t7.CO_EMPR  
 And t1.CO_ENTI = t7.CO_PROV  
 Group By t1.CO_EMPR, t1.CO_ENTI, t1.TI_DOCU_ENTI, t1.NU_DOCU_ENTI, t1.co_banc, t1.nu_cnta_banc, t1.aa_bnco, t1.mm_bnco, t1.ti_movi_banc, t1.nu_comp_banc 
  
Union All  
 Select t1.CO_EMPR, t1.CO_ENTI, Max(t7.NO_CORT_PROV), t1.TI_DOCU_ENTI, t1.NU_DOCU_ENTI,   
  Max(t2.FE_OPER), Max(t2.FE_OPER), Max(t1.FE_DOCU), Max(t1.CO_MONE_DOCU),  
  Max(t2.FA_TIPO_CAMB), Max(t7.NU_RUCS_PROV),   
  Sum (ISNULL(PATINDEX(t1.ST_CIER_DOCU, 'N')* ISNULL(t1.IM_CONV_DOCU,0) +  
  PATINDEX(t1.ST_CIER_DOCU, 'S')* ISNULL(t1.IM_SALD_DOCU,0),0)),  
  (Sum (ISNULL(PATINDEX(t1.ST_CIER_DOCU, 'N')* ISNULL(t1.IM_CONV_DOCU,0) +  
  PATINDEX(t1.ST_CIER_DOCU, 'S')* ISNULL(t1.IM_SALD_DOCU,0),0)) -  
  (Select ISNULL(SUM(ISNULL(t12.IM_PAGO_ORIG,0)),0)  
  From TCAMAR_OCPR t11, TDAMAR_OCPR t12  
  Where t1.CO_EMPR = t12.CO_EMPR  
  And t1.CO_ENTI = t12.CO_ENTI  
  And t1.TI_DOCU_ENTI = t12.CO_TIDO_ORIG  
  And t1.NU_DOCU_ENTI = t12.NU_DOCU_ORIG  
  And t11.TI_SITU != 'ANU'  
  And t11.CO_EMPR = t12.CO_EMPR  
  And t11.CO_UNID_CONC = t12.CO_UNID_CONC  
  And t11.NU_AMAR = t12.NU_AMAR  
  And t11.FE_AMAR = t12.FE_AMAR)) * -1,  
  Max(t2.DE_OBSE), Max(t2.CO_TIPO_OPER)  
 From TDMOVI_CAJA t1, TTDOCU_CNTB t4, TCMOVI_CAJA t2, TMCAJA t3, TMPARA_TESO t6, OFILOGI..TMPROV t7  
 Where t1.TI_ENTI = t6.CO_TIAU_PROV  
 And t1.TI_DOCU_ENTI = t4.TI_DOCU  
 And (t4.ST_DOCU_0015 = 'S'  
 Or  t4.ST_DOCU_0017 = 'S')  
 And t2.TI_SITU != 'ANU'  
 And t1.CO_EMPR = t2.CO_EMPR  
 And t1.CO_CAJA = t2.CO_CAJA  
 And t1.AA_CAJA = t2.AA_CAJA  
 And t1.MM_CAJA = t2.MM_CAJA  
 And t1.TI_MOVI  = t2.TI_MOVI  
 And t1.NU_COMP_CAJA = t2.NU_COMP_CAJA  
 And t1.CO_EMPR = t3.CO_EMPR  
 And t1.CO_CAJA = t3.CO_CAJA  
 And t3.TI_SITU = 'ACT'  
 And t1.CO_EMPR = t6.CO_EMPR  
 And t1.CO_EMPR = t7.CO_EMPR  
 And t1.CO_ENTI = t7.CO_PROV  
 Group By t1.CO_EMPR, t1.CO_ENTI, t1.TI_DOCU_ENTI, t1.NU_DOCU_ENTI  
GO
